<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="tong.li&#39;s blog">
  <meta name="keyword" content="彤哥哥博客，95后技术爱好者,现就职于同程旅行/同程艺龙上海分公司，专注于互联网技术分享的平台。">
  
    <link rel="shortcut icon" href="/css/images/icon.png">
  
  <title>
    
      【MySQL高级实战】-索引与存储引擎学习指南 | 彤哥哥的博客
    
  </title>
  <link href="https://cdn.staticfile.org/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="https://cdn.staticfile.org/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="https://cdn.staticfile.org/highlight.js/9.12.0/styles/tomorrow-night.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
  <script src="https://cdn.staticfile.org/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://cdn.staticfile.org/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="https://cdn.staticfile.org/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
  
  
    <!-- MathJax support START -->
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script type="text/javascript" src="https://cdn.staticfile.org/mathjax/2.7.5/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
    <!-- MathJax support END -->
  


  
  
    
<script src="/js/local-search.js"></script>


<meta name="generator" content="Hexo 5.4.2"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>
  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>彤哥哥的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/series/" class="item-link">分类</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/project/" class="item-link">项目</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于</a>
          
        </li>
      
      
        <li class="menu-item menu-item-search right-list">
    <a role="button" class="popup-trigger">
        <i class="fa fa-search fa-fw"></i>
    </a>
</li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/series/" class="menu-link">分类</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/project/" class="menu-link">项目</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于</a>
            
          </li>
        
      </ul>
    </div>
    
      <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
            <span class="search-icon">
                <i class="fa fa-search"></i>
            </span>
            <div class="search-input-container">
                <input autocomplete="off" autocapitalize="off"
                    placeholder="Please enter your keyword(s) to search." spellcheck="false"
                    type="search" class="search-input">
            </div>
            <span class="popup-btn-close">
                <i class="fa fa-times-circle"></i>
            </span>
        </div>
        <div id="search-result">
            <div id="no-result">
                <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
            </div>
        </div>
    </div>
</div>
    
  </div>
</header>

    <div id="article-banner">
  <h2>【MySQL高级实战】-索引与存储引擎学习指南</h2>
  <p class="post-date">2020-07-29</p>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="1-索引"><a href="#1-索引" class="headerlink" title="1. 索引"></a>1. 索引</h1><h2 id="1-1-索引概述"><a href="#1-1-索引概述" class="headerlink" title="1.1 索引概述"></a>1.1 索引概述</h2><p>打开<a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html">官方索引文档</a>，可以看到MySQL官方对索引的描述:</p>
<blockquote>
<p>The best way to improve the performance of <a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/select.html"><code>SELECT</code></a> operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the <code>WHERE</code> clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.</p>
<p>Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.</p>
</blockquote>
<p>开启翻译模式，大致意思为:</p>
<blockquote>
<p>改善SELECT操作性能的最好方法是在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。</p>
<p>尽管可能会为查询中使用的每个可能的列创建索引，但是不必要的索引会浪费空间和时间，使MySQL难以确定要使用的索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，才能使用最佳索引集来实现快速查询。</p>
</blockquote>
<p>从上述文档我们可以总结出以下几点:</p>
<ul>
<li>索引可以一定程度上解决查询性能问题。</li>
<li>索引项类似于指向表记录的指针，可以快速定位数据。</li>
<li>MySQL所有的数据类型都是可以建索引。</li>
<li>索引对表的更新(INSERT、UPDATE、DELETE)操作不友好，增加表的更新成本。</li>
</ul>
<h2 id="1-2-索引优劣势"><a href="#1-2-索引优劣势" class="headerlink" title="1.2 索引优劣势"></a>1.2 索引优劣势</h2><h3 id="1-2-1-优势"><a href="#1-2-1-优势" class="headerlink" title="1.2.1 优势"></a>1.2.1 优势</h3><ul>
<li>大大提高了数据检索的效率，降低数据库的IO成本。</li>
<li>通过索引列对数据进行排序，降低数据排序的时间成本。  </li>
<li>可创建唯一性索引，保证数据库表中的每一行数据的唯一性。</li>
<li>可以加速表和表之间的连接，实现数据完整性。</li>
</ul>
<h3 id="1-2-2-劣势"><a href="#1-2-2-劣势" class="headerlink" title="1.2.2 劣势"></a>1.2.2 劣势</h3><ul>
<li>索引需要占用物理空间。</li>
<li>创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。</li>
<li>对表中的数据进行更新(INSERT、UPDATE和DELETE)操作的时候，索引也要动态的维护，降低了数据的维护速度。</li>
</ul>
<h2 id="1-3-索引底层结构"><a href="#1-3-索引底层结构" class="headerlink" title="1.3 索引底层结构"></a>1.3 索引底层结构</h2><h3 id="1-3-1-索引与数据结构"><a href="#1-3-1-索引与数据结构" class="headerlink" title="1.3.1 索引与数据结构"></a>1.3.1 索引与数据结构</h3><p><strong>索引(index)是帮助MySQL高效获取数据的有序数据结构。</strong></p>
<p>在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数<br>据结构上实现高级查找算法，这种数据结构就是索引。  </p>
<p>我们都知道二叉搜索树(Binary Search Tree,BST)查询效率很高，这里先举个例子来阐述索引与数据结构的关系,话不多说，直接上图:</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731135417.png" alt="image-20200730163252519"></p>
<p>图1是没有建立索引的数据表，最左边以0x开始的十六进制表示数据记录的物理地址，需要注意的是逻辑上相邻的记录在磁盘上并不一定物理相连。</p>
<p>图2是为了加快Col2列的查找，将图1的数据表中的数据映射到二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。  </p>
<p>图1的数据表没有建立索引，会将表顺序扫描所有记录行，想要查询Col2=34的数据，会很快找到，但是想要查询Col2=3的数据需要全表扫描7次才能找到。试想一下，如果千万量级的数据量这样查询，需要扫描N多个记录行，效率极其低效。</p>
<p>如果是图2建立索引，索引与记录维护(顺序存储)在一棵二叉搜索树，会大大提升查询效率。我们都知道二叉树都是有序的。比根节点小的数据放在左子树的左子节点，比根节点大的数据放在右子树的右子节点。想要查询Col2=34的数据，也会很快找到，想要查询Col2=3的数据，只需要3次即可定位到想要的数据。这样做的本质就是用(额外的索引存储)空间换(查询速度)时间,空间复杂度高了，但是时间复杂度低了。</p>
<p>我们知道二叉搜索树是不平衡树，存放节点也非常的少，面对百万级甚至千万级的表数据显得力不从心，因此MySQL选用B+树来作为索引的存储结构。</p>
<p>通过这个例子，可以利用已有的数据结构来优化数据表的查询。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。  </p>
<h3 id="1-3-2-B树"><a href="#1-3-2-B树" class="headerlink" title="1.3.2 B树"></a>1.3.2 B树</h3><p>B Tree又叫多路平衡搜索树，一颗m阶的BTree特性如下 :</p>
<ul>
<li>树中每个节点最多包含m个孩子。</li>
<li>除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。</li>
<li>若根节点不是叶子节点，则至少有两个孩子,除非树只包含一个节点。</li>
<li>所有的叶子节点都在同一层。</li>
<li>每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] &lt;= n &lt;= m-1，ceil表示向上取整 。</li>
</ul>
<p>以5阶B Tree为例，key的数量：公式推导[ceil(m/2)-1] &lt;= n &lt;= m-1。所以 2 &lt;= n &lt;=4 。当n&gt;4时，中间节点分裂到<br>父节点，两边节点分裂。  </p>
<p>插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。  演变过程如下：  </p>
<ol>
<li><p>插入前4个字母 C N G A  ，排序并插入。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143321.png" alt="image-20200731143321898"></p>
</li>
<li><p>插入H，n&gt;4，中间元素G字母向上分裂到新的节点。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143445.png" alt="image-20200731143445542"></p>
</li>
<li><p>插入E，K，Q不需要分裂。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143547.png" alt="image-20200731143547236"></p>
</li>
<li><p>插入M，中间元素M字母向上分裂到父节点G 。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143707.png" alt="image-20200731143707850"></p>
</li>
<li><p>插入F，W，L，T不需要分裂 。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143735.png" alt="image-20200731143735813"></p>
</li>
<li><p>插入Z，中间元素T向上分裂到父节点中 。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143827.png" alt="image-20200731143827771"></p>
</li>
<li><p>插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143852.png" alt="image-20200731143852737"></p>
</li>
<li><p>最后插入S，NPQR节点n&gt;5，中间节点Q向上分裂，但分裂后父节点DGMT的n&gt;5，中间节点M向上分裂。</p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731143913.png" alt="image-20200731143913548"></p>
</li>
</ol>
<p>到此，该B Tree树就已经构建完成了， B Tree树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。  </p>
<h3 id="1-3-3-B-树"><a href="#1-3-3-B-树" class="headerlink" title="1.3.3 B+树"></a>1.3.3 B+树</h3><p>B+Tree为BTree的变种，B+Tree与BTree的区别为：  </p>
<ul>
<li>n阶B+Tree最多含有n个key，而B Tree最多含有n-1个key。</li>
<li>B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。</li>
<li>所有的非叶子节点都可以看作是key的索引部分。  </li>
</ul>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731144325.png" alt="image-20200731144325332"></p>
<p>由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。  </p>
<h3 id="1-3-4-MySQL-B-树"><a href="#1-3-4-MySQL-B-树" class="headerlink" title="1.3.4 MySQL B+树"></a>1.3.4 MySQL B+树</h3><p>MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指<br>针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。  </p>
<p>MySQL中的 B+Tree 索引结构示意图如下:  </p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200731135440.png" alt="image-20200730170248368"></p>
<h2 id="1-4-索引分类"><a href="#1-4-索引分类" class="headerlink" title="1.4 索引分类"></a>1.4 索引分类</h2><h3 id="1-4-1-聚簇索引"><a href="#1-4-1-聚簇索引" class="headerlink" title="1.4.1 聚簇索引"></a>1.4.1 聚簇索引</h3><p>数据和索引存储到一起，找到索引就获取到了数据。聚簇索引是唯一的，InnoDB一定会有一个聚簇索引来保存数据。</p>
<p>如果一个表中的普通列创建了索引，那么叶子节点中存放的值是聚簇索引的key值。</p>
<p>InnoDB聚簇索引选择顺序：</p>
<ol>
<li>默认选择主键；</li>
<li>没有主机，选择唯一的非空索引；</li>
<li>都没有，则隐式定义一个主键rowId；</li>
</ol>
<h3 id="1-4-2-非聚簇索引"><a href="#1-4-2-非聚簇索引" class="headerlink" title="1.4.2 非聚簇索引"></a>1.4.2 非聚簇索引</h3><p>数据存储和索引分开，叶子节点存储对应的行，需要二次查找，通常称为二级索引或辅助索引；非聚簇索引一定存储有聚簇索引的列值。</p>
<h3 id="1-4-3-单值索引"><a href="#1-4-3-单值索引" class="headerlink" title="1.4.3 单值索引"></a>1.4.3 单值索引</h3><p>即一个索引只包含单个列，一个表可以有多个单列索引  。</p>
<h3 id="1-4-4-唯一索引"><a href="#1-4-4-唯一索引" class="headerlink" title="1.4.4 唯一索引"></a>1.4.4 唯一索引</h3><p>索引列的值必须唯一，但允许有空值  。</p>
<h3 id="1-4-5-复合索引"><a href="#1-4-5-复合索引" class="headerlink" title="1.4.5 复合索引"></a>1.4.5 复合索引</h3><p>一个索引包含多个列  。</p>
<h2 id="1-5-索引语法"><a href="#1-5-索引语法" class="headerlink" title="1.5 索引语法"></a>1.5 索引语法</h2><h3 id="1-5-1-环境准备"><a href="#1-5-1-环境准备" class="headerlink" title="1.5.1 环境准备"></a>1.5.1 环境准备</h3><p>索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">create database index_demo default charset=utf8mb4;</span><br><span class="line"></span><br><span class="line">use index_demo;</span><br><span class="line"></span><br><span class="line">CREATE TABLE `city` (</span><br><span class="line">    `city_id` int(11) NOT NULL AUTO_INCREMENT,</span><br><span class="line">    `city_name` varchar(50) NOT NULL,</span><br><span class="line">    `country_id` int(11) NOT NULL,</span><br><span class="line">    PRIMARY KEY (`city_id`)</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</span><br><span class="line"></span><br><span class="line">CREATE TABLE `country` (</span><br><span class="line">    `country_id` int(11) NOT NULL AUTO_INCREMENT,</span><br><span class="line">    `country_name` varchar(100) NOT NULL,</span><br><span class="line">    PRIMARY KEY (`country_id`)</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</span><br><span class="line"></span><br><span class="line">insert into `city` (`city_id`, `city_name`, `country_id`) values(1,&#x27;西安&#x27;,1);</span><br><span class="line">insert into `city` (`city_id`, `city_name`, `country_id`) values(2,&#x27;NewYork&#x27;,2);</span><br><span class="line">insert into `city` (`city_id`, `city_name`, `country_id`) values(3,&#x27;北京&#x27;,1);</span><br><span class="line">insert into `city` (`city_id`, `city_name`, `country_id`) values(4,&#x27;上海&#x27;,1);</span><br><span class="line"></span><br><span class="line">insert into `country` (`country_id`, `country_name`) values(1,&#x27;China&#x27;);</span><br><span class="line">insert into `country` (`country_id`, `country_name`) values(2,&#x27;America&#x27;);</span><br><span class="line">insert into `country` (`country_id`, `country_name`) values(3,&#x27;Japan&#x27;);</span><br><span class="line">insert into `country` (`country_id`, `country_name`) values(4,&#x27;UK&#x27;);</span><br></pre></td></tr></table></figure>



<h3 id="1-5-2-建立索引"><a href="#1-5-2-建立索引" class="headerlink" title="1.5.2 建立索引"></a>1.5.2 建立索引</h3><p>语法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name</span><br><span class="line">[USING index_type]</span><br><span class="line">ON tbl_name(index_col_name,...)</span><br><span class="line"></span><br><span class="line">index_col_name : column_name[(length)][ASC | DESC]</span><br></pre></td></tr></table></figure>

<p>示例 ： 为city表中的city_name字段创建索引</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; create index idx_city_name on city(city_name);</span><br><span class="line">Query OK, 0 rows affected (0.26 sec)</span><br><span class="line">Records: 0  Duplicates: 0  Warnings: 0</span><br></pre></td></tr></table></figure>

<h3 id="1-5-3-查看索引"><a href="#1-5-3-查看索引" class="headerlink" title="1.5.3 查看索引"></a>1.5.3 查看索引</h3><p>语法：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show index from table_name;</span><br></pre></td></tr></table></figure>

<p>示例： 查看city表的索引</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; show index from city\G;</span><br><span class="line">*************************** 1. row ***************************</span><br><span class="line">        Table: city</span><br><span class="line">   Non_unique: 0</span><br><span class="line">     Key_name: PRIMARY</span><br><span class="line"> Seq_in_index: 1</span><br><span class="line">  Column_name: city_id</span><br><span class="line">    Collation: A</span><br><span class="line">  Cardinality: 4</span><br><span class="line">     Sub_part: NULL</span><br><span class="line">       Packed: NULL</span><br><span class="line">         Null: </span><br><span class="line">   Index_type: BTREE</span><br><span class="line">      Comment: </span><br><span class="line">Index_comment: </span><br><span class="line">      Visible: YES</span><br><span class="line">   Expression: NULL</span><br><span class="line">*************************** 2. row ***************************</span><br><span class="line">        Table: city</span><br><span class="line">   Non_unique: 1</span><br><span class="line">     Key_name: idx_city_name</span><br><span class="line"> Seq_in_index: 1</span><br><span class="line">  Column_name: city_name  </span><br><span class="line">    Collation: A</span><br><span class="line">  Cardinality: 4</span><br><span class="line">     Sub_part: NULL</span><br><span class="line">       Packed: NULL</span><br><span class="line">         Null: </span><br><span class="line">   Index_type: BTREE</span><br><span class="line">      Comment: </span><br><span class="line">Index_comment: </span><br><span class="line">      Visible: YES</span><br><span class="line">   Expression: NULL</span><br><span class="line">2 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>

<h3 id="1-5-4-删除索引"><a href="#1-5-4-删除索引" class="headerlink" title="1.5.4 删除索引"></a>1.5.4 删除索引</h3><p>语法 ：  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP INDEX index_name ON tbl_name;</span><br></pre></td></tr></table></figure>

<p>示例 ： 想要删除city表上的索引idx_city_name，可以操作如下：  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; drop index idx_city_name on city;</span><br><span class="line">Query OK, 0 rows affected (0.16 sec)</span><br><span class="line">Records: 0  Duplicates: 0  Warnings: 0</span><br></pre></td></tr></table></figure>

<h3 id="1-5-5-ALTER命令操作索引"><a href="#1-5-5-ALTER命令操作索引" class="headerlink" title="1.5.5 ALTER命令操作索引"></a>1.5.5 ALTER命令操作索引</h3><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL</span><br><span class="line">alter table tb_name add primary key(column_list);</span><br><span class="line">-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）</span><br><span class="line">alter table tb_name add unique index_name(column_list);</span><br><span class="line">-- 添加普通索引， 索引值可以出现多次。</span><br><span class="line">alter table tb_name add index index_name(column_list);</span><br><span class="line">-- 该语句指定了索引为FULLTEXT， 用于全文索引</span><br><span class="line">alter table tb_name add fulltext index_name(column_list);</span><br></pre></td></tr></table></figure>

<h2 id="1-6-索引设计原则"><a href="#1-6-索引设计原则" class="headerlink" title="1.6 索引设计原则"></a>1.6 索引设计原则</h2><p>索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。  </p>
<ul>
<li><p>对查询频次较高，且数据量比较大的表建立索引。  </p>
</li>
<li><p>索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。  </p>
</li>
<li><p>使用唯一索引，区分度越高，使用索引的效率越高。  </p>
</li>
<li><p>索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。  </p>
</li>
<li><p>使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。  </p>
</li>
<li><p>利用最左前缀,N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。如下创建复合索引，相当于对name创建索引、对name , email创建了索引、对name , email, status创建了索引。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">CREATE INDEX idx_name_email_status ON tb_user(name,email,status);</span><br></pre></td></tr></table></figure></li>
</ul>
<h1 id="2-存储引擎"><a href="#2-存储引擎" class="headerlink" title="2. 存储引擎"></a>2. 存储引擎</h1><h2 id="2-1-存储引擎概述"><a href="#2-1-存储引擎概述" class="headerlink" title="2.1 存储引擎概述"></a>2.1 存储引擎概述</h2><p>和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。  </p>
<p>存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。  </p>
<p>Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。  </p>
<p>MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。  </p>
<p>可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; show engines;</span><br><span class="line">+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br><span class="line">| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |</span><br><span class="line">+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br><span class="line">| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |</span><br><span class="line">| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |</span><br><span class="line">| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |</span><br><span class="line">| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |</span><br><span class="line">| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |</span><br><span class="line">| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |</span><br><span class="line">| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |</span><br><span class="line">| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |</span><br><span class="line">| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |</span><br><span class="line">+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br></pre></td></tr></table></figure>

<p>创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。  查看Mysql数据库默认的存储引擎 ，执行如下指令 ：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; show variables like &#x27;%storage_engine%&#x27;;</span><br><span class="line">+---------------------------------+-----------+</span><br><span class="line">| Variable_name                   | Value     |</span><br><span class="line">+---------------------------------+-----------+</span><br><span class="line">| default_storage_engine          | InnoDB    |</span><br><span class="line">| default_tmp_storage_engine      | InnoDB    |</span><br><span class="line">| disabled_storage_engines        |           |</span><br><span class="line">| internal_tmp_mem_storage_engine | TempTable |</span><br><span class="line">+---------------------------------+-----------+</span><br><span class="line">4 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>

<h2 id="2-2-不同存储引擎的区别"><a href="#2-2-不同存储引擎的区别" class="headerlink" title="2.2 不同存储引擎的区别"></a>2.2 不同存储引擎的区别</h2><table>
<thead>
<tr>
<th>特点</th>
<th>InnoDB</th>
<th>MyISAM</th>
<th>MEMORY</th>
<th>MERGE</th>
<th>NDB</th>
</tr>
</thead>
<tbody><tr>
<td>存储限制</td>
<td>64TB</td>
<td>有</td>
<td>有</td>
<td>没有</td>
<td>有</td>
</tr>
<tr>
<td>事务安全</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>锁机制</td>
<td>行锁(适合高并发)</td>
<td>表锁</td>
<td>表锁</td>
<td>表锁</td>
<td>行锁</td>
</tr>
<tr>
<td>B树索引</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
</tr>
<tr>
<td>哈希索引</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>全文索引</td>
<td>支持(5.6版本之后)</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>集群索引</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>数据索引</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
<td></td>
<td></td>
</tr>
<tr>
<td>索引缓存</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
<td>支持</td>
</tr>
<tr>
<td>数据可压缩</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>空间使用</td>
<td>高</td>
<td>低</td>
<td>N/A</td>
<td>低</td>
<td>低</td>
</tr>
<tr>
<td>内存使用</td>
<td>高</td>
<td>低</td>
<td>中等</td>
<td>低</td>
<td>高</td>
</tr>
<tr>
<td>批量插入速度</td>
<td>低</td>
<td>高</td>
<td>高</td>
<td>高</td>
<td>高</td>
</tr>
<tr>
<td>支持外键</td>
<td>支持</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody></table>
<h2 id="2-3-存储引擎的选择原则"><a href="#2-3-存储引擎的选择原则" class="headerlink" title="2.3 存储引擎的选择原则"></a>2.3 存储引擎的选择原则</h2><p>在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。  </p>
<ul>
<li>InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。</li>
<li>MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。</li>
<li>MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。</li>
<li>MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。  </li>
</ul>
<h2 id="2-4-常见的存储引擎"><a href="#2-4-常见的存储引擎" class="headerlink" title="2.4 常见的存储引擎"></a>2.4 常见的存储引擎</h2><h3 id="2-4-1-InnoDB"><a href="#2-4-1-InnoDB" class="headerlink" title="2.4.1 InnoDB"></a>2.4.1 InnoDB</h3><p>InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。</p>
<ul>
<li><p>事务控制 </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table goods_innodb(</span><br><span class="line">    id int NOT NULL AUTO_INCREMENT,</span><br><span class="line">    name varchar(20) NOT NULL,</span><br><span class="line">    primary key(id)</span><br><span class="line">)ENGINE=innodb DEFAULT CHARSET=utf8mb4;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; start transaction;</span><br><span class="line">Query OK, 0 rows affected (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; insert into goods_innodb(id,name)values(null,&#x27;华为Mate 30 Pro&#x27;);</span><br><span class="line">Query OK, 1 row affected (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt;  select * from goods_innodb;  -- 当前客户端A可以查到自己未提交的事务数据</span><br><span class="line">+----+-------------------+</span><br><span class="line">| id | name              |</span><br><span class="line">+----+-------------------+</span><br><span class="line">|  1 | 华为Mate 30 Pro   |</span><br><span class="line">+----+-------------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from goods_innodb; -- 重新启动另一个客户端B进行查询，其他事务未提交的数据看不到</span><br><span class="line">Empty set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; commit;  -- 当前客户端A事务提交</span><br><span class="line">Query OK, 0 rows affected (0.04 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from goods_innodb; -- 重新在客户端B进行查询，就可以查询到客户端A提交的数据</span><br><span class="line">+----+-------------------+</span><br><span class="line">| id | name              |</span><br><span class="line">+----+-------------------+</span><br><span class="line">|  1 | 华为Mate 30 Pro   |</span><br><span class="line">+----+-------------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure></li>
<li><p>外键约束  </p>
<p>MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 要求父表必须有对应的索引,子表在创建外键的时候，也会自动的创建对应的索引。  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">create table country_innodb(   -- 主表</span><br><span class="line">    country_id int NOT NULL AUTO_INCREMENT, -- 主键索引</span><br><span class="line">    country_name varchar(100) NOT NULL,</span><br><span class="line">    primary key(country_id)</span><br><span class="line">)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</span><br><span class="line"></span><br><span class="line">create table city_innodb( -- 子表</span><br><span class="line">    city_id int NOT NULL AUTO_INCREMENT,</span><br><span class="line">    city_name varchar(50) NOT NULL,</span><br><span class="line">    country_id int NOT NULL, </span><br><span class="line">    primary key(city_id),</span><br><span class="line">    key idx_fk_country_id(country_id),  </span><br><span class="line">    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES -- 外键</span><br><span class="line">    country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE</span><br><span class="line">)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;</span><br><span class="line"></span><br><span class="line">insert into country_innodb values(null,&#x27;China&#x27;),(null,&#x27;America&#x27;),(null,&#x27;Japan&#x27;);</span><br><span class="line">insert into city_innodb values(null,&#x27;Xian&#x27;,1),(null,&#x27;NewYork&#x27;,2),(null,&#x27;ShangHai&#x27;,1);</span><br><span class="line"></span><br><span class="line">mysql&gt; show create table city_innodb\G;  -- 查看city_innodb表结构</span><br><span class="line">*************************** 1. row ***************************</span><br><span class="line">       Table: city_innodb</span><br><span class="line">Create Table: CREATE TABLE `city_innodb` (</span><br><span class="line">  `city_id` int NOT NULL AUTO_INCREMENT,</span><br><span class="line">  `city_name` varchar(50) NOT NULL,</span><br><span class="line">  `country_id` int NOT NULL,</span><br><span class="line">  PRIMARY KEY (`city_id`),</span><br><span class="line">  KEY `idx_fk_country_id` (`country_id`),</span><br><span class="line">  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE</span><br><span class="line">) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure>

<p>在创建索引时,可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL和NO ACTION。  </p>
<p>RESTRICT和NO ACTION相同， 是指限制在子表有关联记录的情况下， 父表不能更新；  </p>
<p>CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；  </p>
<p>SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。  </p>
<p>针对上面创建的两个表， 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果子表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果子表有对应记录， 则子表对应更新 。  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from country_innodb; -- country_innodb所有记录</span><br><span class="line">+------------+--------------+</span><br><span class="line">| country_id | country_name |</span><br><span class="line">+------------+--------------+</span><br><span class="line">|          1 | China        |</span><br><span class="line">|          2 | America      |</span><br><span class="line">|          3 | Japan        |</span><br><span class="line">+------------+--------------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from city_innodb; -- city_innodb所有记录</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">| city_id | city_name | country_id |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">|       1 | Xian      |          1 |</span><br><span class="line">|       2 | NewYork   |          2 |</span><br><span class="line">|       3 | ShangHai  |          1 |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure>



<p>删除country_id为1的country数据会删除失败，原因是有外键的限制。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; delete from country_innodb where country_id = 1;</span><br><span class="line">ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`index_demo`.`city_innodb`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)</span><br></pre></td></tr></table></figure>

<p>更新主表country表的字段country_id ，同时也会将city_innodb表的外键字段country_id更新。</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from country_innodb where country_id = 1; -- 查询country_id = 1的记录</span><br><span class="line">+------------+--------------+</span><br><span class="line">| country_id | country_name |</span><br><span class="line">+------------+--------------+</span><br><span class="line">|          1 | China        |</span><br><span class="line">+------------+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from city_innodb where country_id = 1; -- 查询city_innodb且外键city_innodb=1的记录</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">| city_id | city_name | country_id |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">|       1 | Xian      |          1 |</span><br><span class="line">|       3 | ShangHai  |          1 |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; update country_innodb set country_id = 100 where country_id = 1; -- 更新country_id = 1记录的主键为100</span><br><span class="line">Query OK, 1 row affected (0.06 sec)</span><br><span class="line">Rows matched: 1  Changed: 1  Warnings: 0</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from country_innodb where country_id = 100; -- 查看是否更新成功</span><br><span class="line">+------------+--------------+</span><br><span class="line">| country_id | country_name |</span><br><span class="line">+------------+--------------+</span><br><span class="line">|        100 | China        |</span><br><span class="line">+------------+--------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from city_innodb; -- 查询外键是否也更新成功</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">| city_id | city_name | country_id |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">|       1 | Xian      |        100 |</span><br><span class="line">|       2 | NewYork   |          2 |</span><br><span class="line">|       3 | ShangHai  |        100 |</span><br><span class="line">+---------+-----------+------------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br></pre></td></tr></table></figure></li>
<li><p>存储方式  </p>
<p>nnoDB 存储表和索引有以下两种方式 ：  </p>
<p>①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。  </p>
<p>②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。<br>③. MySQL8开始删除了原来的frm文件，并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据字典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。 如何可以查看表结构信息，官方提供了一个工具叫做ibd2sdi，可以离线的将ibd文件中的冗余存储的sdi信息提取出来，并以json的格式输出到终端。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br><span class="line">331</span><br><span class="line">332</span><br><span class="line">333</span><br><span class="line">334</span><br><span class="line">335</span><br><span class="line">336</span><br><span class="line">337</span><br><span class="line">338</span><br><span class="line">339</span><br><span class="line">340</span><br><span class="line">341</span><br><span class="line">342</span><br><span class="line">343</span><br><span class="line">344</span><br><span class="line">345</span><br><span class="line">346</span><br><span class="line">347</span><br><span class="line">348</span><br><span class="line">349</span><br><span class="line">350</span><br><span class="line">351</span><br><span class="line">352</span><br><span class="line">353</span><br><span class="line">354</span><br><span class="line">355</span><br><span class="line">356</span><br><span class="line">357</span><br><span class="line">358</span><br><span class="line">359</span><br><span class="line">360</span><br><span class="line">361</span><br><span class="line">362</span><br><span class="line">363</span><br><span class="line">364</span><br><span class="line">365</span><br><span class="line">366</span><br><span class="line">367</span><br><span class="line">368</span><br><span class="line">369</span><br><span class="line">370</span><br><span class="line">371</span><br><span class="line">372</span><br><span class="line">373</span><br><span class="line">374</span><br><span class="line">375</span><br><span class="line">376</span><br><span class="line">377</span><br><span class="line">378</span><br><span class="line">379</span><br><span class="line">380</span><br></pre></td><td class="code"><pre><span class="line">  [root@litong ~]<span class="comment"># ls /data/mysql/mall # mysql8以下的存储方式，frm文件存放表的元数据:表结构，ibd存放数据和索引信息</span></span><br><span class="line">  city.frm  city.frm    country.ibd  country.frm </span><br><span class="line">  </span><br><span class="line">  [root@litong master]<span class="comment"># ll data/index_demo/  # mysql8已经删除了frm文件，只有ibd文件</span></span><br><span class="line">  总用量 480</span><br><span class="line">  -rw-r----- 1 root root 131072 7月  31 15:39 city.ibd</span><br><span class="line">  -rw-r----- 1 root root 131072 8月   3 10:43 city_innodb.ibd</span><br><span class="line">  -rw-r----- 1 root root 114688 7月  31 15:26 country.ibd</span><br><span class="line">-rw-r----- 1 root root 114688 8月   3 10:43 country_innodb.ibd</span><br><span class="line">  -rw-r----- 1 root root 114688 8月   3 10:23 goods_innodb.ibd</span><br><span class="line">  [root@litong data]<span class="comment"># ibd2sdi index_demo/city_innodb.ibd # 通过ibd2sdi工具输出sdi信息</span></span><br><span class="line">  [<span class="string">&quot;ibd2sdi&quot;</span></span><br><span class="line">  ,</span><br><span class="line">  &#123;</span><br><span class="line">  	<span class="string">&quot;type&quot;</span>: 1,</span><br><span class="line">  	<span class="string">&quot;id&quot;</span>: 365,</span><br><span class="line">  	<span class="string">&quot;object&quot;</span>:</span><br><span class="line">  		&#123;</span><br><span class="line">      <span class="string">&quot;mysqld_version_id&quot;</span>: 80021,</span><br><span class="line">      <span class="string">&quot;dd_version&quot;</span>: 80021,</span><br><span class="line">      <span class="string">&quot;sdi_version&quot;</span>: 80019,</span><br><span class="line">      <span class="string">&quot;dd_object_type&quot;</span>: <span class="string">&quot;Table&quot;</span>,</span><br><span class="line">      <span class="string">&quot;dd_object&quot;</span>: &#123;</span><br><span class="line">          <span class="string">&quot;name&quot;</span>: <span class="string">&quot;city_innodb&quot;</span>,</span><br><span class="line">          <span class="string">&quot;mysql_version_id&quot;</span>: 80021,</span><br><span class="line">          <span class="string">&quot;created&quot;</span>: 20200803023043,</span><br><span class="line">          <span class="string">&quot;last_altered&quot;</span>: 20200803023043,</span><br><span class="line">          <span class="string">&quot;hidden&quot;</span>: 1,</span><br><span class="line">          <span class="string">&quot;options&quot;</span>: <span class="string">&quot;avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;columns&quot;</span>: [</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;city_id&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 4,</span><br><span class="line">                  <span class="string">&quot;is_nullable&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_zerofill&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_unsigned&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_auto_increment&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;is_virtual&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;char_length&quot;</span>: 11,</span><br><span class="line">                  <span class="string">&quot;numeric_precision&quot;</span>: 10,</span><br><span class="line">                  <span class="string">&quot;numeric_scale&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale_null&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;datetime_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;datetime_precision_null&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;has_no_default&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_null&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;default_value&quot;</span>: <span class="string">&quot;AAAAAA==&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;update_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;interval_count=0;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;table_id=1070;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;column_key&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;column_type_utf8&quot;</span>: <span class="string">&quot;int&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [],</span><br><span class="line">                  <span class="string">&quot;collation_id&quot;</span>: 255,</span><br><span class="line">                  <span class="string">&quot;is_explicit_collation&quot;</span>: <span class="literal">false</span></span><br><span class="line">              &#125;,</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;city_name&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 16,</span><br><span class="line">                  <span class="string">&quot;is_nullable&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_zerofill&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_unsigned&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_auto_increment&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_virtual&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;char_length&quot;</span>: 200,</span><br><span class="line">                  <span class="string">&quot;numeric_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;datetime_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;datetime_precision_null&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;has_no_default&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_null&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;default_value&quot;</span>: <span class="string">&quot;AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;update_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;interval_count=0;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;table_id=1070;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;column_key&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;column_type_utf8&quot;</span>: <span class="string">&quot;varchar(50)&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [],</span><br><span class="line">                  <span class="string">&quot;collation_id&quot;</span>: 255,</span><br><span class="line">                  <span class="string">&quot;is_explicit_collation&quot;</span>: <span class="literal">false</span></span><br><span class="line">              &#125;,</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;country_id&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 4,</span><br><span class="line">                  <span class="string">&quot;is_nullable&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_zerofill&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_unsigned&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_auto_increment&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_virtual&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 3,</span><br><span class="line">                  <span class="string">&quot;char_length&quot;</span>: 11,</span><br><span class="line">                  <span class="string">&quot;numeric_precision&quot;</span>: 10,</span><br><span class="line">                  <span class="string">&quot;numeric_scale&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale_null&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;datetime_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;datetime_precision_null&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;has_no_default&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_null&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;default_value&quot;</span>: <span class="string">&quot;AAAAAA==&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;update_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;interval_count=0;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;table_id=1070;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;column_key&quot;</span>: 4,</span><br><span class="line">                  <span class="string">&quot;column_type_utf8&quot;</span>: <span class="string">&quot;int&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [],</span><br><span class="line">                  <span class="string">&quot;collation_id&quot;</span>: 255,</span><br><span class="line">                  <span class="string">&quot;is_explicit_collation&quot;</span>: <span class="literal">false</span></span><br><span class="line">              &#125;,</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;DB_TRX_ID&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 10,</span><br><span class="line">                  <span class="string">&quot;is_nullable&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_zerofill&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_unsigned&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_auto_increment&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_virtual&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 4,</span><br><span class="line">                  <span class="string">&quot;char_length&quot;</span>: 6,</span><br><span class="line">                  <span class="string">&quot;numeric_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;datetime_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;datetime_precision_null&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;has_no_default&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;default_value&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;update_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;table_id=1070;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;column_key&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;column_type_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [],</span><br><span class="line">                  <span class="string">&quot;collation_id&quot;</span>: 63,</span><br><span class="line">                  <span class="string">&quot;is_explicit_collation&quot;</span>: <span class="literal">false</span></span><br><span class="line">              &#125;,</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;DB_ROLL_PTR&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 9,</span><br><span class="line">                  <span class="string">&quot;is_nullable&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_zerofill&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_unsigned&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_auto_increment&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_virtual&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 5,</span><br><span class="line">                  <span class="string">&quot;char_length&quot;</span>: 7,</span><br><span class="line">                  <span class="string">&quot;numeric_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;numeric_scale_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;datetime_precision&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;datetime_precision_null&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;has_no_default&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;srs_id&quot;</span>: 0,</span><br><span class="line">                  <span class="string">&quot;default_value&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8_null&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;default_value_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;default_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;update_option&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;generation_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;table_id=1070;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;column_key&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;column_type_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [],</span><br><span class="line">                  <span class="string">&quot;collation_id&quot;</span>: 63,</span><br><span class="line">                  <span class="string">&quot;is_explicit_collation&quot;</span>: <span class="literal">false</span></span><br><span class="line">              &#125;</span><br><span class="line">          ],</span><br><span class="line">          <span class="string">&quot;schema_ref&quot;</span>: <span class="string">&quot;index_demo&quot;</span>,</span><br><span class="line">          <span class="string">&quot;se_private_id&quot;</span>: 1070,</span><br><span class="line">          <span class="string">&quot;engine&quot;</span>: <span class="string">&quot;InnoDB&quot;</span>,</span><br><span class="line">          <span class="string">&quot;last_checked_for_upgrade_version_id&quot;</span>: 0,</span><br><span class="line">          <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;autoinc=0;version=0;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;row_format&quot;</span>: 2,</span><br><span class="line">          <span class="string">&quot;partition_type&quot;</span>: 0,</span><br><span class="line">          <span class="string">&quot;partition_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;partition_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;default_partitioning&quot;</span>: 0,</span><br><span class="line">          <span class="string">&quot;subpartition_type&quot;</span>: 0,</span><br><span class="line">          <span class="string">&quot;subpartition_expression&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;subpartition_expression_utf8&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;default_subpartitioning&quot;</span>: 0,</span><br><span class="line">          <span class="string">&quot;indexes&quot;</span>: [</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;PRIMARY&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_generated&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;flags=0;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;id=155;root=4;space_id=13;table_id=1070;trx_id=3855;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;algorithm&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;is_algorithm_explicit&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_visible&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;engine&quot;</span>: <span class="string">&quot;InnoDB&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 0</span><br><span class="line">                      &#125;,</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4294967295,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 3</span><br><span class="line">                      &#125;,</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 3,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4294967295,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 4</span><br><span class="line">                      &#125;,</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 4,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4294967295,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 1</span><br><span class="line">                      &#125;,</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 5,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4294967295,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 2</span><br><span class="line">                      &#125;</span><br><span class="line">                  ],</span><br><span class="line">                  <span class="string">&quot;tablespace_ref&quot;</span>: <span class="string">&quot;index_demo/city_innodb&quot;</span></span><br><span class="line">              &#125;,</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;idx_fk_country_id&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;hidden&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_generated&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;options&quot;</span>: <span class="string">&quot;flags=0;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;id=156;root=5;space_id=13;table_id=1070;trx_id=3855;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;type&quot;</span>: 3,</span><br><span class="line">                  <span class="string">&quot;algorithm&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;is_algorithm_explicit&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                  <span class="string">&quot;is_visible&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                  <span class="string">&quot;engine&quot;</span>: <span class="string">&quot;InnoDB&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;secondary_engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">false</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 2</span><br><span class="line">                      &#125;,</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;length&quot;</span>: 4294967295,</span><br><span class="line">                          <span class="string">&quot;order&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;hidden&quot;</span>: <span class="literal">true</span>,</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 0</span><br><span class="line">                      &#125;</span><br><span class="line">                  ],</span><br><span class="line">                  <span class="string">&quot;tablespace_ref&quot;</span>: <span class="string">&quot;index_demo/city_innodb&quot;</span></span><br><span class="line">              &#125;</span><br><span class="line">          ],</span><br><span class="line">          <span class="string">&quot;foreign_keys&quot;</span>: [</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;name&quot;</span>: <span class="string">&quot;fk_city_country&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;match_option&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;update_rule&quot;</span>: 3,</span><br><span class="line">                  <span class="string">&quot;delete_rule&quot;</span>: 2,</span><br><span class="line">                  <span class="string">&quot;unique_constraint_name&quot;</span>: <span class="string">&quot;PRIMARY&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;referenced_table_catalog_name&quot;</span>: <span class="string">&quot;def&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;referenced_table_schema_name&quot;</span>: <span class="string">&quot;index_demo&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;referenced_table_name&quot;</span>: <span class="string">&quot;country_innodb&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;elements&quot;</span>: [</span><br><span class="line">                      &#123;</span><br><span class="line">                          <span class="string">&quot;column_opx&quot;</span>: 2,</span><br><span class="line">                          <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                          <span class="string">&quot;referenced_column_name&quot;</span>: <span class="string">&quot;country_id&quot;</span></span><br><span class="line">                      &#125;</span><br><span class="line">                  ]</span><br><span class="line">              &#125;</span><br><span class="line">          ],</span><br><span class="line">          <span class="string">&quot;check_constraints&quot;</span>: [],</span><br><span class="line">          <span class="string">&quot;partitions&quot;</span>: [],</span><br><span class="line">          <span class="string">&quot;collation_id&quot;</span>: 255</span><br><span class="line">      &#125;</span><br><span class="line">  &#125;</span><br><span class="line">  &#125;</span><br><span class="line">  ,</span><br><span class="line">  &#123;</span><br><span class="line">  	<span class="string">&quot;type&quot;</span>: 2,</span><br><span class="line">  	<span class="string">&quot;id&quot;</span>: 18,</span><br><span class="line">  	<span class="string">&quot;object&quot;</span>:</span><br><span class="line">  		&#123;</span><br><span class="line">      <span class="string">&quot;mysqld_version_id&quot;</span>: 80021,</span><br><span class="line">      <span class="string">&quot;dd_version&quot;</span>: 80021,</span><br><span class="line">      <span class="string">&quot;sdi_version&quot;</span>: 80019,</span><br><span class="line">      <span class="string">&quot;dd_object_type&quot;</span>: <span class="string">&quot;Tablespace&quot;</span>,</span><br><span class="line">      <span class="string">&quot;dd_object&quot;</span>: &#123;</span><br><span class="line">          <span class="string">&quot;name&quot;</span>: <span class="string">&quot;index_demo/city_innodb&quot;</span>,</span><br><span class="line">          <span class="string">&quot;comment&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;options&quot;</span>: <span class="string">&quot;encryption=N;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;flags=16417;id=13;server_version=80021;space_version=1;state=normal;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;engine&quot;</span>: <span class="string">&quot;InnoDB&quot;</span>,</span><br><span class="line">          <span class="string">&quot;engine_attribute&quot;</span>: <span class="string">&quot;&quot;</span>,</span><br><span class="line">          <span class="string">&quot;files&quot;</span>: [</span><br><span class="line">              &#123;</span><br><span class="line">                  <span class="string">&quot;ordinal_position&quot;</span>: 1,</span><br><span class="line">                  <span class="string">&quot;filename&quot;</span>: <span class="string">&quot;./index_demo/city_innodb.ibd&quot;</span>,</span><br><span class="line">                  <span class="string">&quot;se_private_data&quot;</span>: <span class="string">&quot;id=13;&quot;</span></span><br><span class="line">              &#125;</span><br><span class="line">          ]</span><br><span class="line">      &#125;</span><br><span class="line">  &#125;</span><br><span class="line">  &#125;</span><br><span class="line">  ]</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="2-4-2-MyISAM"><a href="#2-4-2-MyISAM" class="headerlink" title="2.4.2 MyISAM"></a>2.4.2 MyISAM</h3><p>MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。  </p>
<ul>
<li><p>不支持事务  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table goods_myisam(</span><br><span class="line">    id int NOT NULL AUTO_INCREMENT,</span><br><span class="line">    name varchar(20) NOT NULL,</span><br><span class="line">    primary key(id)</span><br><span class="line">)ENGINE=myisam DEFAULT CHARSET=utf8mb4;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; start transaction; # 开启事务</span><br><span class="line">Query OK, 0 rows affected (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; insert into goods_myisam(id,name)values(null,&#x27;华为P40 Pro&#x27;); # 插入一条数据</span><br><span class="line">Query OK, 1 row affected (0.03 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from goods_myisam; # 查看已插入的数据</span><br><span class="line">+----+---------------+</span><br><span class="line">| id | name          |</span><br><span class="line">+----+---------------+</span><br><span class="line">|  1 | 华为P40 Pro   |</span><br><span class="line">+----+---------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; rollback;  # 回滚事务</span><br><span class="line">Query OK, 0 rows affected, 1 warning (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from goods_myisam; # 此时再查询，我们发现事务没有回滚，说明不支持事务</span><br><span class="line">+----+---------------+</span><br><span class="line">| id | name          |</span><br><span class="line">+----+---------------+</span><br><span class="line">|  1 | 华为P40 Pro   |</span><br><span class="line">+----+---------------+</span><br><span class="line">1 row in set (0.00 sec)</span><br></pre></td></tr></table></figure></li>
<li><p>存储方式  </p>
<p>每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是 ： .frm (表结构定义文件)、.MYD(my data数据存储文件)、MYI(My Index , 存储索引)；  </p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">[root@litong data]# ll /test/ # mysql5.7的存储方式</span><br><span class="line">total 20</span><br><span class="line">-rw-r----- 1 mysql mysql   67 Aug  3 13:57 db.opt</span><br><span class="line">-rw-r----- 1 mysql mysql 8586 Aug  3 13:58 goods_myisam.frm</span><br><span class="line">-rw-r----- 1 mysql mysql    0 Aug  3 13:58 goods_myisam.MYD</span><br><span class="line">-rw-r----- 1 mysql mysql 1024 Aug  3 13:58 goods_myisam.MYI</span><br><span class="line">[root@litong data]# ls index_demo/ | grep goods_myisam;  # mysql8的.frm文件已经改为了.sdi文件</span><br><span class="line">-rw-r----- 1 root root   2898 8月   3 11:47 goods_myisam_366.sdi</span><br><span class="line">-rw-r----- 1 root root     24 8月   3 11:48 goods_myisam.MYD</span><br><span class="line">-rw-r----- 1 root root   2048 8月   3 11:48 goods_myisam.MYI</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="2-4-3-MEMORY"><a href="#2-4-3-MEMORY" class="headerlink" title="2.4.3 MEMORY"></a>2.4.3 MEMORY</h3><p>Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。</p>
<p>MEMORY类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的数据就会丢失。  </p>
<h3 id="2-4-4-MERGE"><a href="#2-4-4-MERGE" class="headerlink" title="2.4.4 MERGE"></a>2.4.4 MERGE</h3><p>MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。</p>
<p>对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。</p>
<p>可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。  </p>
<p><img src="https://ltyeamin.github.io/imgs/2020/07/20200803140839.png" alt="image-20200803140837993"></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">create table order_1990(            -- order分表1</span><br><span class="line">    order_id int ,</span><br><span class="line">    order_money decimal(10,2),</span><br><span class="line">    order_address varchar(50),</span><br><span class="line">    primary key (order_id)</span><br><span class="line">)engine = myisam default charset=utf8mb4;</span><br><span class="line"></span><br><span class="line">create table order_1991(           -- order分表2</span><br><span class="line">    order_id int ,</span><br><span class="line">    order_money decimal(10,2),</span><br><span class="line">    order_address varchar(50),</span><br><span class="line">    primary key (order_id)</span><br><span class="line">)engine = myisam default charset=utf8mb4;</span><br><span class="line"></span><br><span class="line">create table order_all(             -- order合并的主表</span><br><span class="line">    order_id int ,</span><br><span class="line">    order_money decimal(10,2),</span><br><span class="line">    order_address varchar(50),</span><br><span class="line">    primary key (order_id)</span><br><span class="line">)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default -- 默认插入的数据会在最后一张表中插入</span><br><span class="line">charset=utf8mb4;</span><br><span class="line"></span><br><span class="line">insert into order_1990 values(1,100.0,&#x27;北京&#x27;);</span><br><span class="line">insert into order_1990 values(2,100.0,&#x27;上海&#x27;);</span><br><span class="line"></span><br><span class="line">insert into order_1991 values(10,200.0,&#x27;北京&#x27;);</span><br><span class="line">insert into order_1991 values(11,200.0,&#x27;上海&#x27;);</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; select * from order_1990; -- 查询order_1990的数据</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|        1 |      100.00 | 北京          |</span><br><span class="line">|        2 |      100.00 | 上海          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from order_1991; -- 查询order_1991的数据</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|       10 |      200.00 | 北京          |</span><br><span class="line">|       11 |      200.00 | 上海          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from order_all; -- 查询order_all的数据</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|        1 |      100.00 | 北京          |</span><br><span class="line">|        2 |      100.00 | 上海          |</span><br><span class="line">|       10 |      200.00 | 北京          |</span><br><span class="line">|       11 |      200.00 | 上海          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">4 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; insert into order_all values(100,10000.0,&#x27;西安&#x27;); -- 为order_all插入一条数据</span><br><span class="line">Query OK, 1 row affected (0.03 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from order_all;  -- 查询是否插入成功</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|        1 |      100.00 | 北京          |</span><br><span class="line">|        2 |      100.00 | 上海          |</span><br><span class="line">|       10 |      200.00 | 北京          |</span><br><span class="line">|       11 |      200.00 | 上海          |</span><br><span class="line">|      100 |    10000.00 | 西安          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">5 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from order_1991; -- 插进了order_1991这张分表中,由于在MERGE表定义时，INSERT_METHOD选择的是LAST那么插入的数据会向最后一张表中插入。</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|       10 |      200.00 | 北京          |</span><br><span class="line">|       11 |      200.00 | 上海          |</span><br><span class="line">|      100 |    10000.00 | 西安          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">3 rows in set (0.00 sec)</span><br><span class="line"></span><br><span class="line">mysql&gt; select * from order_1990; -- 并没有插入到order_1990表中</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">| order_id | order_money | order_address |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">|        1 |      100.00 | 北京          |</span><br><span class="line">|        2 |      100.00 | 上海          |</span><br><span class="line">+----------+-------------+---------------+</span><br><span class="line">2 rows in set (0.00 sec)</span><br><span class="line"></span><br></pre></td></tr></table></figure>



<h1 id="3-面试连环炮"><a href="#3-面试连环炮" class="headerlink" title="3. 面试连环炮"></a>3. 面试连环炮</h1><ol>
<li>为什么MySQL索引结构选用B+树？为什么不选用其他数据结构?</li>
<li>什么时候该建立索引，什么时候不该建立索引?</li>
<li>聚簇索引和非聚簇索引的区别？</li>
<li>索引的分类有哪些？</li>
<li>聚簇索引一定是唯一索引吗？</li>
<li>请解释回表、覆盖索引、最左匹配、索引下推。</li>
</ol>
<h1 id="4-参考资料"><a href="#4-参考资料" class="headerlink" title="4. 参考资料"></a>4. 参考资料</h1><ul>
<li><a target="_blank" rel="noopener" href="https://dev.mysql.com/doc/refman/8.0/en/">MySQL 8.0 Reference Manual</a></li>
<li><a target="_blank" rel="noopener" href="https://www.cs.usfca.edu/~galles/visualization/Algorithms.html">数据结构可视化</a></li>
<li><a target="_blank" rel="noopener" href="https://visualgo.net/zh">数据结构与算法可视化</a></li>
</ul>
</section>
    <!-- Tags START -->
    
      <div class="tags">
        <span>Tags:</span>
        
  <a href="/tags#MySQL" >
    <span class="tag-code">MySQL</span>
  </a>

      </div>
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/2020/07/22/%E3%80%90Java%E5%B9%B6%E5%8F%91%E7%BC%96%E7%A8%8B%E5%AE%9E%E6%88%98%E3%80%91-J.U.C%E5%B9%B6%E5%8F%91%E5%8C%85%E4%B9%8BAQS/">
        <span class="nav-arrow">← </span>
        
          【Java并发编程实战】-解读J.U.C并发包之AQS
        
      </a>
    
    
      <a class="nav-right" href="/2020/07/31/%E5%B7%A7%E7%94%A8Gitee+PicGo+Typora%E6%90%AD%E5%BB%BA%E5%85%8D%E8%B4%B9%E5%9B%BE%E5%BA%8A/">
        
          巧用Gitee+PicGo+Typora搭建免费图床
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">若你觉得我的文章对你有帮助，欢迎点击上方按钮对我打赏</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Utterances START -->
      <div id="utterances"></div>
      <script src="https://utteranc.es/client.js"
        repo="ltyeamin/blogtalks"
        issue-term="pathname"
        theme="github-light"
        crossorigin="anonymous"
        async></script>    
      <!-- Utterances END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
    <strong class="toc-title">Catalog</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#1-%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1. 索引</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-1-%E7%B4%A2%E5%BC%95%E6%A6%82%E8%BF%B0"><span class="toc-nav-text">1.1 索引概述</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-2-%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8A%A3%E5%8A%BF"><span class="toc-nav-text">1.2 索引优劣势</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-2-1-%E4%BC%98%E5%8A%BF"><span class="toc-nav-text">1.2.1 优势</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-2-2-%E5%8A%A3%E5%8A%BF"><span class="toc-nav-text">1.2.2 劣势</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-3-%E7%B4%A2%E5%BC%95%E5%BA%95%E5%B1%82%E7%BB%93%E6%9E%84"><span class="toc-nav-text">1.3 索引底层结构</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-3-1-%E7%B4%A2%E5%BC%95%E4%B8%8E%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84"><span class="toc-nav-text">1.3.1 索引与数据结构</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-3-2-B%E6%A0%91"><span class="toc-nav-text">1.3.2 B树</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-3-3-B-%E6%A0%91"><span class="toc-nav-text">1.3.3 B+树</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-3-4-MySQL-B-%E6%A0%91"><span class="toc-nav-text">1.3.4 MySQL B+树</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-4-%E7%B4%A2%E5%BC%95%E5%88%86%E7%B1%BB"><span class="toc-nav-text">1.4 索引分类</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4-1-%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.4.1 聚簇索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4-2-%E9%9D%9E%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.4.2 非聚簇索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4-3-%E5%8D%95%E5%80%BC%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.4.3 单值索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4-4-%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.4.4 唯一索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-4-5-%E5%A4%8D%E5%90%88%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.4.5 复合索引</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-5-%E7%B4%A2%E5%BC%95%E8%AF%AD%E6%B3%95"><span class="toc-nav-text">1.5 索引语法</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-1-%E7%8E%AF%E5%A2%83%E5%87%86%E5%A4%87"><span class="toc-nav-text">1.5.1 环境准备</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-2-%E5%BB%BA%E7%AB%8B%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.5.2 建立索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-3-%E6%9F%A5%E7%9C%8B%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.5.3 查看索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-4-%E5%88%A0%E9%99%A4%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.5.4 删除索引</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#1-5-5-ALTER%E5%91%BD%E4%BB%A4%E6%93%8D%E4%BD%9C%E7%B4%A2%E5%BC%95"><span class="toc-nav-text">1.5.5 ALTER命令操作索引</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-6-%E7%B4%A2%E5%BC%95%E8%AE%BE%E8%AE%A1%E5%8E%9F%E5%88%99"><span class="toc-nav-text">1.6 索引设计原则</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#2-%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="toc-nav-text">2. 存储引擎</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E6%A6%82%E8%BF%B0"><span class="toc-nav-text">2.1 存储引擎概述</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-2-%E4%B8%8D%E5%90%8C%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E7%9A%84%E5%8C%BA%E5%88%AB"><span class="toc-nav-text">2.2 不同存储引擎的区别</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-3-%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E7%9A%84%E9%80%89%E6%8B%A9%E5%8E%9F%E5%88%99"><span class="toc-nav-text">2.3 存储引擎的选择原则</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-4-%E5%B8%B8%E8%A7%81%E7%9A%84%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="toc-nav-text">2.4 常见的存储引擎</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-1-InnoDB"><span class="toc-nav-text">2.4.1 InnoDB</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-2-MyISAM"><span class="toc-nav-text">2.4.2 MyISAM</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-3-MEMORY"><span class="toc-nav-text">2.4.3 MEMORY</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-4-MERGE"><span class="toc-nav-text">2.4.4 MERGE</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#3-%E9%9D%A2%E8%AF%95%E8%BF%9E%E7%8E%AF%E7%82%AE"><span class="toc-nav-text">3. 面试连环炮</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#4-%E5%8F%82%E8%80%83%E8%B5%84%E6%96%99"><span class="toc-nav-text">4. 参考资料</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://example.com/2020/07/29/【MySQL高级实战】-索引与存储引擎学习指南/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', '/css/images/error_icon.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== '/css/images/error_icon.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>






    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
  <p class="copyright">
    &copy; 2024 | Proudly powered by <a href="https://hexo.io" target="_blank">Hexo</a>
    <br>
    Theme by <a target="_blank" rel="noopener" href="https://github.com/ltyeamin">tong.li</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("https://cdn.staticfile.org/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("https://cdn.staticfile.org/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      hljs.configure({useBR: true});
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>
<!-- Baidu Tongji -->



<script src='https://cdn.staticfile.org/mermaid/8.11.2/mermaid.min.js'></script>



<script src="/js/script.js"></script>


  </body>
</html>